package com.joinway.platform.utils;

import java.io.StringReader;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Comparator;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.TreeSet;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.IOUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;

import com.joinway.framework.bean.domain.constant.SqlConstants;
import com.joinway.framework.extension.groovy.DomainUtils;
import com.joinway.platform.bean.ColumnField;

@Deprecated
public class DdlEntity {

	final static String CREATE_TABLE_PATTERN = "(?i)\\s*CREATE\\s+TABLE\\s+`?(\\w+)`?.*";
	final static String COLUMN_PATTERN = "(?i)\\s*`?(?<name>\\w+)`?\\s+(?<type>\\w+)(?<size>\\(\\d+(,\\d+)?\\))?\\s+(?<sign>UNSIGNED)?.*";
	final static String PRIMARY_KEY_PATTERN = "(?i)\\s*PRIMARY\\s+KEY\\s+\\(\\s*(?<keys>.+)\\s*\\)";
	
	/**
	 * non column name key words 
	 */
	final static List<String> COLUMN_EX_KEYS = Arrays.asList(new String[]{"id", "create", "primary", "unique"});
	
	final static Pattern tableNamePattern = Pattern.compile(CREATE_TABLE_PATTERN);
	final static Pattern columnNamePattern = Pattern.compile(COLUMN_PATTERN);
	final static Pattern primaryKeyPattern = Pattern.compile(PRIMARY_KEY_PATTERN);
	
	final static Map<String, String> typeMap = new HashMap<>();
	final static Map<String, String> importTypeMap = new HashMap<>();
	final static Map<String, String> importAnnMap = new HashMap<>();
	final static Map<String, String> annMap = new HashMap<>();
	
	static{
		typeMap.put("varchar", "String");
		typeMap.put("time", "Date");
		typeMap.put("timestamp", "Date");
		typeMap.put("datetime", "Date");
		typeMap.put("date", "Date");
		typeMap.put("year", "Date");
		typeMap.put("tinyint", "int");
		typeMap.put("tinyint(1)", "boolean");
		typeMap.put("smallint", "int");
		typeMap.put("mediumint", "int");
		typeMap.put("int", "long");
		typeMap.put("bigint", "long");
		typeMap.put("decimal", "BigDecimal");
		typeMap.put("int unsigned", "long");
		typeMap.put("bigint unsigned", "BigInteger");
		typeMap.put("char", "String");
		typeMap.put("blob", "byte[]");
		typeMap.put("tinyblob", "byte[]");
		typeMap.put("mediumblob", "byte[]");
		typeMap.put("longblob", "byte[]");
		typeMap.put("text", "String");
		typeMap.put("tinytext", "String");
		typeMap.put("mediumtext", "String");
		typeMap.put("longtext", "String");
		typeMap.put("int unsigned", "long");
		typeMap.put("bit(1)", "boolean");
		typeMap.put("bit", "byte[]");
		typeMap.put("binary", "byte[]");
		typeMap.put("varbinary", "byte[]");
		
		importTypeMap.put("Date", "java.util.Date");
		importTypeMap.put("BigDecimal", "java.math.BigDecimal");
		importTypeMap.put("BigInteger", "java.math.BigInteger");
		
		importAnnMap.put("TINYINT(1) UNSIGNED", "com.joinway.bean.domain.converter.TinyintConverter");
		
		annMap.put("TINYINT(1) UNSIGNED", "@DomainField(converter=TinyintConverter.class)");
	}

	protected List<String> lines;
	
	protected ColumnField idField;
	
	protected List<ColumnField> primaryKeys;
	
	protected List<ColumnField> nonIdPrimaryKeys;
	
	/**
	 * normal fields and non cust id fields
	 */
	protected List<ColumnField> nonDefaultIdFields;
	
	protected String tableName;
	
	protected String domainName;
	
	protected List<ColumnField> fields;
	
	public DdlEntity(String ddl) throws Exception {
		lines = IOUtils.readLines(new StringReader(ddl));
		
		constructFields(lines);
		tableName = getTableName(lines);
		domainName = getDomainName(lines);
		
		primaryKeys = Arrays.asList(
				fields.stream().filter(f -> f.isPrimaryKey()).toArray(ColumnField[]::new)
		);
		
		nonIdPrimaryKeys = Arrays.asList(
				primaryKeys.stream().filter(pk -> pk.isPrimaryKey() && !pk.isIdKey()).toArray(ColumnField[]::new)
		);
		
		nonDefaultIdFields = Arrays.asList(
				fields.stream().filter(f -> !f.isIdKey() || f.isIdKey() && f.isCustIdKey()).toArray(ColumnField[]::new)
		);
	}
	
	public static String parseToDomainName(String sql) throws Exception {
		return getDomainName(IOUtils.readLines(new StringReader(sql)));
	}
	
	public String getTableName(){
		return tableName;
	}
	
	public String getDomainName(){
		return domainName;
	}
	
	public ColumnField getIdField(){
		return idField;
	}
	
	public List<ColumnField> getPrimaryKeys(){
		return primaryKeys;
	}
	
	public List<ColumnField> getNonIdPrimaryKeys(){
		return nonIdPrimaryKeys;
	}

	public List<ColumnField> getNonDefaultIdFields() {
		return nonDefaultIdFields;
	}

	public List<ColumnField> getFields(){
		return fields;
	}
	
	public static Set<String> getImports(List<ColumnField> fields, ImportFor... import4){
		Set<String> imports = new TreeSet<>(new Comparator<String>(){
			@Override
			public int compare(String s1, String s2) {
				return s1.compareTo(s2);
			}
		});
		
		if(CollectionUtils.isNotEmpty(fields) && ArrayUtils.isNotEmpty(import4)){
			for(ImportFor i4 : import4){
				fields.stream()
					.filter(f -> i4.map().containsKey(f.getType()))
					.forEach(f -> imports.add(i4.map().get(f.getType())));
			}
		}
		
		return imports;
	}
	
	static String getDomainName(List<String> lines){
		return StringUtils.capitalize(DomainUtils.convertToDomainFieldName(getTableName(lines)));		
	}
	
	static String getTableName(List<String> lines){
		String table = null;
		
		for(String line : lines){
			Matcher m = tableNamePattern.matcher(line.toUpperCase().replaceAll("IF\\s+NOT\\s+EXISTS", ""));
			
			if(m.matches() && m.groupCount() > 0){
				table = m.group(1);
				break;
			}
		}
		
		return StringUtils.upperCase(table);		
	}
	
	protected void constructFields(List<String> lines){
		fields = new ArrayList<>();
		
		List<String> pks = new ArrayList<>();
		
		for(String line : lines){
			Matcher columnNameMatcher = columnNamePattern.matcher(line);
			Matcher pkMatcher = primaryKeyPattern.matcher(line);
			
			if(columnNameMatcher.matches() && columnNameMatcher.groupCount() > 1){
				String name = columnNameMatcher.group("name").toLowerCase();
				
				// construct auto increment primary key
				if(line.toUpperCase().contains("AUTO_INCREMENT")){
					// id column which was expected as auto increment field
					idField = createColumnField(columnNameMatcher);
					idField.setPrimaryKey(true);
					idField.setIdKey(true);
					idField.setCustIdKey(!SqlConstants.Key.Id.equalsIgnoreCase(idField.getColumn()));
					
					fields.add(idField);
				}else if(pkMatcher.matches()){
					String[] keys = pkMatcher.group("keys").replaceAll("`", "").split(",");
					
					for(String key : keys){
						pks.add(key.trim().toUpperCase());
					}
				}
				
				// construct other fields
				if(!COLUMN_EX_KEYS.contains(name)){
					ColumnField field = createColumnField(columnNameMatcher);
					
					if(!fields.contains(field)){
						fields.add(field);
					}
				}
			}
		}
		
		// reset primary key flag
		fields.stream().filter(f -> pks.contains(f.getColumn())).forEach(f -> f.setPrimaryKey(true));
	}

	protected ColumnField createColumnField(Matcher columnNameMatcher){
		ColumnField field = new ColumnField();
		
		String name = columnNameMatcher.group("name");
		field.setName(DomainUtils.convertToDomainFieldName(name));
		field.setColumn(name.toUpperCase());
		
		String typeText = columnNameMatcher.group("type").toLowerCase();
		String sizeText = columnNameMatcher.group("size");
		String signText = columnNameMatcher.group("sign");
		
		String type = null;
		
		do{
			type = typeMap.get(typeText + sizeText);
			if(StringUtils.isNotBlank(type)) break;
			
			type = typeMap.get(typeText + " " + signText);
			if(StringUtils.isNotBlank(type)) break;
			
			type = typeMap.get(typeText);
			if(StringUtils.isNotBlank(type)) break;
			
			type = typeText;
		}while(false);
		
		field.setType(type);
		
		String dbType = typeText + sizeText + " " + signText;
		field.setDbType(dbType);
		
		String ann = annMap.get(dbType.toUpperCase());
		if(StringUtils.isNotBlank(ann)){
			field.setAnn(ann);
		}
		
		return field;
	}
}
